﻿Imports System.Data.OleDb

Public Class AddRecipe
    Inherits System.Web.UI.Page

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

    End Sub

    Protected Sub btn_AddRecipe_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btn_AddRecipe.Click

        Dim d As DateTime = Now

        Dim myGUID As New System.Guid
        myGUID = Guid.NewGuid()
        Dim newFileName = myGUID.ToString() + ".jpg"

        If fup_RecipeImage.HasFile Then
            fup_RecipeImage.SaveAs(Request.MapPath("Image") & "/" & newFileName)
        End If

        Dim oleDbConn As New OleDb.OleDbConnection(ConfigurationManager.ConnectionStrings("recipeConnectionString").ConnectionString)
        Dim SqlString As String = "INSERT into RECIPE(REC_NAME,TYPE_ID,REC_STARS,REC_SERVES,REC_TIME,REC_DESC,REC_VIDURL,REC_IMAGE,REC_DATE) Values (@f1,@f2,@f3,@f4,@f5,@f6,@f7,@f8,@f9)"
        Dim cmd As OleDbCommand = New OleDbCommand(SqlString, oleDbConn)
        cmd.CommandType = CommandType.Text
        cmd.Parameters.AddWithValue("@f1", tb_RecipeName.Text)
        cmd.Parameters.AddWithValue("@f2", ddl_RecipeType.SelectedValue)
        cmd.Parameters.AddWithValue("@f3", rbl_RecipeStars.SelectedValue)
        cmd.Parameters.AddWithValue("@f4", tb_RecipeServes.Text)
        cmd.Parameters.AddWithValue("@f5", tb_RecipeTime.Text)
        cmd.Parameters.AddWithValue("@f6", tb_RecipeDescription.Text)
        cmd.Parameters.AddWithValue("@f7", tb_RecipeVidURL.Text)
        cmd.Parameters.AddWithValue("@f8", "Image/" & newFileName)
        cmd.Parameters.AddWithValue("@f9", d.ToString("yyyy-MM-dd"))
        oleDbConn.Open()
        cmd.ExecuteNonQuery()

        'Getting ID of the recipe just added so that it will be used to populate REC_ID in RECIPE_TAG table
        Dim newcmd As New OleDbCommand("SELECT @@IDENTITY", oleDbConn)
        Dim uid As Integer
        uid = newcmd.ExecuteScalar

        Dim oleDbConnTag As New OleDb.OleDbConnection(ConfigurationManager.ConnectionStrings("recipeConnectionString").ConnectionString)
        Dim SqlStringTag As String = "INSERT into RECIPE_TAG(REC_ID,TAG_ID) Values (@f1,@f2)"
        oleDbConnTag.Open()
        For Each cb As ListItem In chk_RecipeTag.Items
            If cb.Selected Then
                'here is where the join to the table of tags and recipes is happening
                Dim cmdTag As OleDbCommand = New OleDbCommand(SqlStringTag, oleDbConnTag)
                cmdTag.CommandType = CommandType.Text
                'going through all checkboxes and associating the value behind the checkbox with the value of the recipe
                cmdTag.Parameters.AddWithValue("@f1", uid)
                cmdTag.Parameters.AddWithValue("@f2", cb.Value)
                cmdTag.ExecuteNonQuery()
            End If
        Next


        'If the NewTags is empty - do not add any new tags:
        If tb_RecipeNewTags.Text = "" Then Return

        'This will take the content of the textbox and break it into an array of words
        Dim newTags = tb_RecipeNewTags.Text.Split(",")

        'Go through each new tag and add it to the table of tags
        For Each newTag In newTags

            '1. Add the new tag to the tag table
            Dim newTagSql As String = "INSERT INTO Tag(Tag) VALUES(@f1)"
            Dim cmdTag As OleDbCommand = New OleDbCommand(newTagSql, oleDbConn)
            cmdTag.CommandType = CommandType.Text
            cmdTag.Parameters.AddWithValue("@f1", newTag)
            cmdTag.ExecuteNonQuery()

            '2. Find the ID of the new tag in the tag table - 
            'The last item has the largest ID number. - Therefore SELECT TOP 1 in Table ORDERBY TAG_ID DESC
            Dim getLastIdCmd As OleDbCommand = New OleDbCommand("SELECT TOP 1 TAG_ID from TAG ORDER BY TAG_ID DESC", oleDbConn)
            'using ExecuteReader since I need to get a value back
            Dim dr = getLastIdCmd.ExecuteReader()
            dr.Read()
            Dim lastId = dr.GetValue(0)

            '3. Associate this last ID with the Recipe ID - this is like the first for loop above
            Dim cmdRecipe As OleDbCommand = New OleDbCommand(SqlStringTag, oleDbConn)
            cmdRecipe.CommandType = CommandType.Text
            cmdRecipe.Parameters.AddWithValue("@f1", uid)
            'The tag foreign key ID is the ID we have just fetched
            cmdRecipe.Parameters.AddWithValue("@f2", lastId)
            cmdRecipe.ExecuteNonQuery()

        Next


    End Sub
End Class